package com.estore.service.persitence;

import com.estore.service.model.Item;
import com.estore.service.persitence.core.MySQLDatabaseConnector;
import com.estore.service.util.exception.EStoreException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ItemDatabaseManager {

    private Connection conn;
    private ResultSet result;

    public boolean insertItem(Item item) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertItem = null;
        try {
            insertItem = conn.prepareStatement("insert into item set"
                    + " name=?, batchnumber=?, dateofpurchase=?, description=?,"
                    + " serialnumber=?, depreciationrate=?");
            insertItem.setString(1, item.getName());
            insertItem.setString(2, item.getBatchNumber());
            insertItem.setString(3, item.getDateOfPurchase());
            insertItem.setString(4, item.getDescription());
            insertItem.setString(5, item.getSerialNumber());
            insertItem.setDouble(6, item.getDepreciationRate());
            insertItem.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean updateItem(Item item) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement updateItem = null;
        try {
            updateItem = conn.prepareStatement("update item set "
                    + " name=?, batchnumber=?, dateofpurchase=?, description=?,"
                    + " serialnumber=?, depreciationrate=? where id=?");
            updateItem.setString(1, item.getName());
            updateItem.setString(2, item.getBatchNumber());
            updateItem.setString(3, item.getDateOfPurchase());
            updateItem.setString(4, item.getDescription());
            updateItem.setString(5, item.getSerialNumber());
            updateItem.setDouble(6, item.getDepreciationRate());
            updateItem.setInt(7, item.getId());
            updateItem.executeUpdate();
        } catch (SQLException e) {
            inserted = false;
            throw new EStoreException(e);
        }
        return inserted;
    }

    public Item getItemById(int id) {
        Item item= new Item();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectItemById = null;
        try {
            selectItemById = conn.prepareStatement("select * from item where id=?");
            selectItemById.setInt(1, id);
            result = selectItemById.executeQuery();
            while (result.next()) {
                item = getItem(result);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return item;
    }

    public Item getItem(ResultSet result) {
        Item item = new Item();
        try {
            item = new Item();
            item.setName(result.getString("name"));
            item.setBatchNumber(result.getString("batchnumber"));
            item.setDateOfPurchase(result.getString("dateofpurchase"));
            item.setDepreciationRate(result.getDouble("depreciationrate"));
            item.setDescription(result.getString("description"));
            item.setSerialNumber(result.getString("serialnumber"));
            item.setId(result.getInt("id"));
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return item;
    }

    public List<Item> getAllItems(int start, int size) {
        List<Item> items = new ArrayList<Item>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllItems = null;
        try {
            selectAllItems = conn.prepareStatement("select * from item LIMIT ?,?");
            selectAllItems.setInt(1, start);
            selectAllItems.setInt(2, size);
            result = selectAllItems.executeQuery();
            while (result.next()) {
                items.add(getItem(result));
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return items;
    }

    public int getAllItemCount() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllItemCount = null;
        try {
            selectAllItemCount = conn.prepareStatement("select count(*) from item");
            result = selectAllItemCount.executeQuery();
            while (result.next()) {
                return result.getInt(1);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return 0;

    }
}
